ASP.NET Core Blazor | Custom Data Sorting

If your application deals with huge volumes of data, performing sorting and paging on the client-side is not a recommended approach. Trying to load the entire dataset onto the client machine in one-go may explode both, the browser memory and rendering engine.

So, the recommended approach is to perform sorting and paging on the database server. This is the approach that we typically use in real world web applications especially if the applications deal with huge volumes of data. In this video, let's discuss implementing both sorting and paging on the database server.

Server side API must provide sorted and paged data

1. Server side API or data access layer must be able to provide sorted and paged data.
2. skip parameter specifies the number of rows to skip and take parameter specifies the number of rows to retrieve.
3. orderBy parameter specifies the list of columns by which the data must be sorted.
4. GetEmployees() method returns sorted and paged data.

EmployeesController.cs

[Route("api/[controller]")]
[ApiController]
public class EmployeesController : ControllerBase
{
    private readonly IEmployeeRepository employeeRepository;

    public EmployeesController(IEmployeeRepository employeeRepository)
    {
        this.employeeRepository = employeeRepository;
    }

    [HttpGet]
    public async Task<ActionResult> GetEmployees(int skip = 0, int take = 5, string orderBy = "EmployeeId")
    {
        try
        {
            return Ok(await employeeRepository.GetEmployees(skip, take, orderBy));
        }
        catch (Exception)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error retrieving data from the database");
        }
    }
}

Dynamic LINQ

Use dynamic Linq to be able to pass string parameters to OrderBy and Where LINQ methods. The following is the command to install the dynamic LINQ nuget package.

Install-Package System.Linq.Dynamic.Core -Version 1.2.12

IEmployeeRepository.cs

public interface IEmployeeRepository
{
    Task<EmployeeDataResult> GetEmployees(int skip, int take, string orderBy);
}

EmployeeRepository.cs

public class EmployeeRepository : IEmployeeRepository
{
    private readonly AppDbContext appDbContext;

    public EmployeeRepository(AppDbContext appDbContext)
    {
        this.appDbContext = appDbContext;
    }

    public async Task<EmployeeDataResult> GetEmployees(int skip = 0, int take = 5, string orderBy = "EmployeeId")
    {
        EmployeeDataResult result = new EmployeeDataResult()
        {
            Employees = appDbContext.Employees.OrderBy(orderBy).Skip(skip).Take(take),
            Count = await appDbContext.Employees.CountAsync()
        };

        return result;
    }
}

EmployeeDataResult.cs

public class EmployeeDataResult
{
    public IEnumerable<Employee> Employees { get; set; }
    public int Count { get; set; }
}

Changes in Blazor Client Project

@page "/"
@using BlazorProject.Shared
@using Syncfusion.Blazor.Grids

<SfGrid TValue="Employee" AllowPaging="true" AllowSorting="true">
    <SfDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="@typeof(EmployeeAdaptor)"></SfDataManager>
    <GridPageSettings PageSize="5" PageSizes="@pagerDropdown" PageCount="3"></GridPageSettings>
    <GridSortSettings AllowUnsort="false">
        <GridSortColumns>
            <GridSortColumn Field="EmployeeId"></GridSortColumn>
        </GridSortColumns>
    </GridSortSettings>
    <GridColumns>
        <GridColumn Field=@nameof(Employee.EmployeeId) HeaderText="ID"></GridColumn>
        <GridColumn Field=@nameof(Employee.FirstName) HeaderText="First Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.LastName) HeaderText=" Last Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.Email) HeaderText="Email"></GridColumn>
    </GridColumns>
</SfGrid>

@code{
    public string[] pagerDropdown { get; set; } = new string[] { "All", "5", "10", "15", "20" };
}

• On the Syncfusion DataGrid set AllowPaging and AllowSorting property to true.
• Use SfDataManager with a Custom Adaptor to retrieve server side sorted and paged data.
• It's the Custom Adaptor that calls the server side REST API and provides data to the DataGrid.
• Syncfusion DataAdaptor abstract class is the base class for our custom Adaptor (EmployeeAdaptor).
• The DataAdaptor abstract class has both synchronous and asynchronous variations.
• We are overriding ReadAsync() method to manually call the server side REST API and provide data to the DataGrid.
• Sorted property of DataManagerRequest object contains the list of Sort objects.
• Sort object has Column and Directtion property. Column is the name of the column by which the user is trying to sort data in the datagrid.
• Direction property specifies ascending or descending.
• The list returned by Sorted property contains multiple Sort objects, if multi-column sorting is enabled and if the user sorts by multiple columns.
• Reverse the List<Sort> objects so the data is sorted in the correct column order.

EmployeeAdaptor.cs

public class EmployeeAdaptor : DataAdaptor
{
    private readonly IEmployeeService employeeService;

    public EmployeeAdaptor(IEmployeeService employeeService)
    {
        this.employeeService = employeeService;
    }
        
    public async override Task<object> ReadAsync(DataManagerRequest dataManagerRequest, string key = null)
    {
        string orderByString = null;
            
        if(dataManagerRequest.Sorted != null)
        {
            List<Sort> sortList = dataManagerRequest.Sorted;
            sortList.Reverse();
            orderByString = string.Join(",", sortList.Select(s => string.Format("{0} {1}", s.Name, s.Direction)));
        }
            
        EmployeeDataResult result = 
            await employeeService.GetEmployees(dataManagerRequest.Skip, dataManagerRequest.Take, orderByString);

        DataResult dataResult = new DataResult()
        {
            Result = result.Employees,
            Count = result.Count
        };

        return dataResult;
    }
}

Inject service into Custom Adaptor

Since we are injecting a service (IEmployeeService) into Custom Adaptor, we need to register Custom Adaptor using AddScoped() method in Program.cs file.

public class Program
{
    public static async Task Main(string[] args)
    {
        var builder = WebAssemblyHostBuilder.CreateDefault(args);
        builder.Services.AddSyncfusionBlazor();
        builder.Services.AddScoped<EmployeeAdaptor>();
        await builder.Build().RunAsync();
    }
}

Tying the Custom Adaptor and DataGrid together

<SfDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="@typeof(EmployeeAdaptor)"></SfDataManager>

IEmployeeService.cs

public interface IEmployeeService
{
    Task<EmployeeDataResult> GetEmployees(int skip, int take, string orderBy);
}

EmployeeService.cs

public class EmployeeService : IEmployeeService
{
    private readonly HttpClient httpClient;

    public EmployeeService(HttpClient httpClient)
    {
        this.httpClient = httpClient;
    }

    public async Task<EmployeeDataResult> GetEmployees(int skip, int take, string orderBy)
    {
        return await httpClient.GetFromJsonAsync<EmployeeDataResult>
                ($"/api/employees?skip={skip}&take={take}&orderBy={orderBy}");
    }
}